# -*- coding: utf-8 -*-
#各种查询，决策分析，报表
class StockController < ApplicationController
  before_filter :employee_authorize
  before_filter :sys_before_log
  before_filter :employee_action_hx
  before_filter :privilege_filter,:except=>[:statisticsSaleDeliveryView,:statisticsPurchaseEntryView,:simpleStockForToolTipByProductId]

  #库管分析决策
  def analysis

  end
  
  #库存查询主页
  def index

  end

  #综合库存查询
  def productQuery
    start = params[:start]
    limit = params[:limit]
    begin      
      queryStr = "pc.is_locked != 1"
      subQueryStr = "is_locked != 1"
      queryHash = Hash.new
      productId = params[:product_id]
      productTypeId = params[:product_type_id]
      warehouseId = params[:warehouse_id]
      #是否显示详细批次，即是否显示库存中的货，是通过什么途径来的。
      batchInfo = params[:batch_info]
      actionDate = params[:action_date]

      #只有在类别这空的情况下，才考虑产品ID
      if productTypeId == ""
        if productId != "" && productId != "null"
          queryStr = "pc.product_id = :product_id"
          subQueryStr = "product_id = #{productId}"
          queryHash[:product_id] = productId
        end
      else
        queryStr = "p.product_type_id in (:product_type_id)"
        #subQueryStr = "p.product_type_id in (#{productTypeId})"
        queryHash[:product_type_id] = productTypeId
      end
      

      if warehouseId != "" && warehouseId != "null"
        queryStr = queryStr + " AND pc.warehouse_id = :warehouse_id"
        subQueryStr = subQueryStr + " AND warehouse_id = #{warehouseId}"
        queryHash[:warehouse_id] = warehouseId
      end

      if actionDate != ""
        queryStr = queryStr + " AND pc.action_date <= :action_date"
        subQueryStr = subQueryStr + " AND action_date <= #{actionDate}"
        queryHash[:action_date] = actionDate
      end

      #把已经撤销的爬出在外。
      queryStr = queryStr + " AND pc.is_locked = 0"
      subQueryStr = subQueryStr + " AND is_locked = 0"
        
      #这儿是判断是否显示详细批次
      if batchInfo == "true"
        groupCondition = "pc.product_id,pc.warehouse_id,pc.resource_id,pc.resource_type"
        subQueryStr = subQueryStr + " AND auditing_status = '' AND product_id = pc.product_id AND warehouse_id = pc.warehouse_id AND resource_id = pc.resource_id AND resource_type = pc.resource_type"
        selectStr = "pc.id,product_id,warehouse_id,sum(quantity) as projected_quantity,(select sum(quantity) as quantity from product_collections where #{subQueryStr}) as quantity,resource_id,resource_type,pc.description,action_date,pc.created_at"
      else
        groupCondition = "pc.product_id,pc.warehouse_id"
        subQueryStr = subQueryStr + " AND auditing_status = '' AND product_id = pc.product_id AND warehouse_id = pc.warehouse_id"
        selectStr = "pc.id,product_id,warehouse_id,sum(quantity) as projected_quantity,(select sum(quantity) as quantity from product_collections where #{subQueryStr}) as quantity,pc.description,action_date,pc.created_at"
      end
      #这条测试的就好用。
      logger.info "queryStr:" + queryStr
      logger.info "batchInfo:" + batchInfo
      
      if batchInfo == "true"
        productCollectionsCount = ProductCollection.find(:all,:readonly,:conditions=>[queryStr,queryHash],:joins=>' pc left join products p on pc.product_id = p.id', :select => "pc.id" ,:group => groupCondition )
        if params[:sub_type].nil?
          productCollections = ProductCollection.find(:all,:readonly,:include=>[:warehouse,:product,:resource],:conditions=>[queryStr,queryHash], :select => "#{selectStr}" ,:joins=>' pc left join products p on pc.product_id = p.id',:offset => start.to_i,:limit => limit.to_i,:group => groupCondition )
        else
          productCollections = ProductCollection.find(:all,:readonly,:include=>[:warehouse,:product,:resource],:conditions=>[queryStr,queryHash], :select => "#{selectStr}" ,:joins=>' pc left join products p on pc.product_id = p.id',:group => groupCondition )
        end
      else
        productCollectionsCount = ProductCollection.find(:all,:readonly,:conditions=>[queryStr,queryHash], :select => "pc.id" ,:joins=>' pc left join products p on pc.product_id = p.id',:group => groupCondition )
        if params[:sub_type].nil?
          productCollections = ProductCollection.find(:all,:readonly,:include=>[:warehouse,:product],:conditions=>[queryStr,queryHash], :select => "#{selectStr}" ,:joins=>' pc left join products p on pc.product_id = p.id',:offset => start.to_i,:limit => limit.to_i,:group => groupCondition )
        else
          productCollections = ProductCollection.find(:all,:readonly,:include=>[:warehouse,:product],:conditions=>[queryStr,queryHash], :select => "#{selectStr}" ,:joins=>' pc left join products p on pc.product_id = p.id',:group => groupCondition)
        end
      end

      #计算库存产品的总价值。
      costPCs = ProductCollection.find(:first,:readonly,:conditions=>[queryStr,queryHash], :select => "sum(pc.quantity * p.cost_price) as sum_cost_amount,sum(pc.quantity * p.tax_price) as sum_tax_amount" ,:from=>'product_collections pc,products p',:group => "pc.product_id" )
      logger.info "costPCs.............................."
      sumCostAmount = 0
      sumTaxAmount = 0
      if costPCs != nil
        sumCostAmount = costPCs.sum_cost_amount
        sumTaxAmount = costPCs.sum_tax_amount
      end
      logger.info sumCostAmount

      logger.info "productCollectionsCount:"+productCollectionsCount.length.to_s
      #测试数据
      #由于在测试过程中，这个表中会产生垃圾数据，比如出库的数据已经删了，但盘点表中，依然没有删。
      #下面这块代码先屏蔽了，因为现在的记录都是不删的。
      #      faultProductArray = Array.new
      #      productCollections.each do |record|
      #        if record == nil || record.resource == nil
      #          faultProductArray << record
      #        end
      #      end
      #
      #      faultProductArray.each do |record|
      #        productCollections.delete(record)
      #      end
      logger.info productCollections.length
      #jsonStr="{totalProperty:"+productCollectionsCount.length.to_s+",root:"+productCollections.to_json(:include=>[:product,:warehouse,:resource])+"}"
      #判断是输出报表，还是输出Json
      if params[:sub_type].nil?
        jsonStr="{totalProperty:"+productCollectionsCount.length.to_s+",sum_cost_amount:#{sumCostAmount},sum_tax_amount:#{sumTaxAmount}"+",root:"+productCollections.to_json(:include=>{:product=>{:only=>[:id,:name,:number,:cost_price]},:warehouse=>{:only=>[:id,:name]},:resource=>{:only=>[:id,:order_number]}})+"}"
        logger.info jsonStr
        render :text=>jsonStr, :layout=>false
      else
        require 'spreadsheet'
        Spreadsheet.client_encoding = "UTF-8"
        bookString = StringIO.new
        book = Spreadsheet.open "#{Rails.root.to_s}/report_template/stock_query.xls"
        i = 4
        sheet1 = book.worksheet 0
        logger.info productCollections.to_json
        sheet1[3,0] = "产品名称"
        sheet1[3,1] = "产品编号"
        sheet1[3,2] = "实际数量"
        sheet1[3,3] = "预计数量"
        sheet1[3,4] = "仓库名称"
        
        logger.info productCollections.length
        productCollections.each do |productCollection|
          sheet1[i,0] = productCollection.product.name
          sheet1[i,1] = productCollection.product.number
          sheet1[i,2] = productCollection.quantity
          sheet1[i,3] = productCollection.projected_quantity.to_i
          sheet1[i,4] = productCollection.warehouse.name
          i += 1
        end

        #sheet1[i,9] = sumCostPriceAmount
        #sheet1[i,11] = sumTaxPriceAmount

        book.write bookString
        send_data bookString.string,:filename => '库存报表.xls'
      end
      
    rescue Exception => e
      logger.info e
      DbLog.info(session[:company_id], session[:department_id], session[:employee_id], params[:controller], params[:action], request.remote_ip ,e.to_s)
    end
  end

  #得到仓库中的产品数量
  def getProductStockQuantity
    quantity1 =  ProductCollection.where("product_id = ? AND warehouse_id = 1",params[:id]).sum(:quantity)
    quantity2 =  ProductCollection.where("product_id = ? AND warehouse_id = 2",params[:id]).sum(:quantity)
    render :text=>quantity1.to_s + "|" + quantity2.to_s, :layout=>false
  end


  #统计采购入库
  def statisticsPurchaseEntry
    # 首先计算一下成本价。
    #Product.cost_price
    #begin
    start = params[:start]
    limit = params[:limit]
    #以入库时间分析出年，月
    calYear = params[:begin_date].split(/-/)[0]
    calMonth = params[:begin_date].split(/-/)[1].to_i
    conditionsStr = ''

    if params[:purchase_order_number].to_s != ''
      conditionsStr = conditionsStr + "po.seq_number in (#{params[:purchase_order_number]})"
    end

    if params[:auditing_status].to_s != '' && params[:auditing_status] != ' '
      if conditionsStr == ''
        conditionsStr = 'auditing_status like :auditing_status'
      else
        conditionsStr = conditionsStr+' AND auditing_status like :auditing_status'
      end
      conditionsHash[:auditing_status] = '%'+params[:auditing_status].to_s+'%'
    elsif params[:auditing_status] == ' '
      if conditionsStr == ''
        conditionsStr = 'auditing_status = :auditing_status'
      else
        conditionsStr = conditionsStr+' AND auditing_status = :auditing_status'
      end
      conditionsHash[:auditing_status] = ''
    end

    if params[:begin_date].to_s != ''
      if conditionsStr == ''
        conditionsStr = "wed.updated_at >= '#{params[:begin_date]}'"
      else
        conditionsStr = conditionsStr+" AND wed.updated_at >= '#{params[:begin_date]}'"
      end
    end

    if params[:end_date].to_s != ''
      if conditionsStr == ''
        conditionsStr = "wed.updated_at <= '#{params[:end_date]}'"
      else
        conditionsStr = conditionsStr+" AND wed.updated_at <= '#{params[:end_date]}'"
      end
    end

    if params[:vendor_id].to_s != ''
      if conditionsStr == ''
        conditionsStr = "v.id = #{params[:vendor_id]}"
      else
        conditionsStr = conditionsStr+" AND v.id = #{params[:vendor_id]}"
      end
    end

    #显示产品
    if params[:product_id].to_s != ''
      if conditionsStr == ''
        conditionsStr = "wed.product_id = #{params[:product_id]}"
      else
        conditionsStr = conditionsStr+" AND wed.product_id = #{params[:product_id]}"
      end
    end


    if conditionsStr != ""
      conditionsStr = "where " + conditionsStr
    end

    if params[:entry_info] == "true"
      #要对where再处理一下。以不显示数量为0即没有进货的入库详细表信息
      #      if conditionsStr != ""
      #        conditionsStr = conditionsStr + "AND ed.quantity is not null"
      #      else
      #        conditionsStr = "where ed.quantity is not null"
      #      end
      purchaseOrders = PurchaseOrder.find(:all,:select=>"po.id as purchase_order_id,po.auditing_status,po.order_number as purchase_order_number,ed.order_number as entry_order_number,po.action_date ,po.auditing_status ,pd.quantity as purchase_quantity,p.name as product_name,ifnull(ed.quantity,0) as entry_quantity ,d.name as department_name,po.store_status,v.name as vendor_name",:joins =>"po
left join purchase_order_details pd on po.id = pd.purchase_order_id AND pd.is_locked = 0
left join (select we.order_number,ed.purchase_order_id,ed.sale_order_id,ed.product_id,sum(ed.quantity) quantity from warehousing_entries we
           left join warehousing_entry_details ed on we.id = ed.warehousing_entry_id where we.auditing_status != '#{Constants::PASS_APPLY_CANCEL}' group by ed.id) ed
           on pd.sale_order_id = ed.sale_order_id AND pd.product_id = ed.product_id AND pd.purchase_order_id = ed.purchase_order_id
left join products p on p.id = pd.product_id
left join departments d on d.id = po.department_id
left join vendors v on v.id = po.vendor_id
#{conditionsStr}")
    else
      #      purchaseOrders = PurchaseOrder.find(:all,:select=>"po.id as purchase_order_id,po.auditing_status,po.order_number as purchase_order_number,po.action_date ,po.auditing_status ,pd.quantity as purchase_quantity,p.name as product_name,ifnull(ed.quantity,0) as entry_quantity ,d.name as department_name,po.store_status,v.name as vendor_name",:joins =>"po
      #left join purchase_order_details pd on po.id = pd.purchase_order_id AND pd.is_locked = 0
      #left join (select ed.purchase_order_id,ed.sale_order_id,ed.product_id,sum(ed.quantity) quantity from warehousing_entries we
      #           left join warehousing_entry_details ed on we.id = ed.warehousing_entry_id where we.auditing_status != '#{Constants::PASS_APPLY_CANCEL}' group by ed.sale_order_id,ed.purchase_order_id,ed.product_id) ed
      #           on pd.sale_order_id = ed.sale_order_id AND pd.product_id = ed.product_id AND pd.purchase_order_id = ed.purchase_order_id
      #left join products p on p.id = pd.product_id
      #left join departments d on d.id = po.department_id
      #left join vendors v on v.id = po.vendor_id
      #{conditionsStr} group by pd.id,p.id")
      warehousingEntries = WarehousingEntry.find(:all,:select=>"wed.id,we.order_number,we.auditing_status, wed.quantity ,we.updated_at,po.order_number as purchase_order_number,po.auditing_status as purchase_auditing_status,v.name as vendor_name,p.name as product_name,p.number as product_number,cp.cost_price as cost_price,cp.tax_price as tax_price,(wed.quantity * cp.cost_price) as cost_price_amount,(wed.quantity * cp.tax_price) as tax_price_amount" ,:joins =>"we
        left join warehousing_entry_details wed on we.id = wed.warehousing_entry_id
        left join purchase_orders po on po.id = we.purchase_order_id
        left join products p on p.id = wed.product_id
        left join departments d on d.id = po.department_id
        left join vendors v on v.id = po.vendor_id
        left join cost_prices cp on cp.product_id = p.id AND cp.cal_year = #{calYear} AND cp.cal_month = #{calMonth}
        #{conditionsStr} group by wed.id,wed.product_id")
    end
    sumCostPriceAmount = 0.00
    sumTaxPriceAmount = 0.00
    if warehousingEntries.length > 0
      warehousingEntries.each do |warehousingEntry|
        sumCostPriceAmount = sumCostPriceAmount + warehousingEntry.cost_price_amount.to_f
        sumTaxPriceAmount = sumTaxPriceAmount + warehousingEntry.tax_price_amount.to_f
      end
    end
    jsonStr="{totalProperty:"+warehousingEntries.length.to_s+",sum_cost_price_amount:#{sumCostPriceAmount},sum_tax_price_amount:#{sumTaxPriceAmount},root:"+warehousingEntries.to_json()+"}"
    logger.info jsonStr
    render :text=>jsonStr, :layout=>false
  end


  #统计销售出库
  def statisticsSaleDelivery
    # 首先计算一下成本价。
    #Product.cost_price
    #begin
    start = params[:start]
    limit = params[:limit]
    #以入库时间分析出年，月
    calYear = params[:begin_date].split(/-/)[0]
    calMonth = params[:begin_date].split(/-/)[1].to_i
    conditionsStr = ''

    if params[:purchase_order_number].to_s != ''
      conditionsStr = conditionsStr + "so.seq_number in (#{params[:purchase_order_number]})"
    end

    if params[:auditing_status].to_s != '' && params[:auditing_status] != ' '
      if conditionsStr == ''
        conditionsStr = 'auditing_status like :auditing_status'
      else
        conditionsStr = conditionsStr+' AND auditing_status like :auditing_status'
      end
      conditionsHash[:auditing_status] = '%'+params[:auditing_status].to_s+'%'
    elsif params[:auditing_status] == ' '
      if conditionsStr == ''
        conditionsStr = 'auditing_status = :auditing_status'
      else
        conditionsStr = conditionsStr+' AND auditing_status = :auditing_status'
      end
      conditionsHash[:auditing_status] = ''
    end

    if params[:begin_date].to_s != ''
      if conditionsStr == ''
        conditionsStr = "do.action_date >= '#{params[:begin_date]}'"
      else
        conditionsStr = conditionsStr+" AND do.action_date >= '#{params[:begin_date]}'"
      end
    end

    if params[:end_date].to_s != ''
      if conditionsStr == ''
        conditionsStr = "do.action_date <= '#{params[:end_date]}'"
      else
        conditionsStr = conditionsStr+" AND do.action_date <= '#{params[:end_date]}'"
      end
    end

    if params[:customer_id].to_s != '' && params[:customer_id].to_s != "null"
      if conditionsStr == ''
        conditionsStr = "c.id = #{params[:customer_id]}"
      else
        conditionsStr = conditionsStr+" AND c.id = #{params[:customer_id]}"
      end
    end

    #显示产品
    if params[:product_id].to_s != '' && params[:product_id].to_s != 'null'
      if conditionsStr == ''
        conditionsStr = "dod.product_id = #{params[:product_id]}"
      else
        conditionsStr = conditionsStr+" AND dod.product_id = #{params[:product_id]}"
      end
    end

    #把出库的，已经撤销的，去掉。
    if conditionsStr == ''
        conditionsStr = "do.auditing_status != '#{Constants::PASS_APPLY_CANCEL}'"
      else
        conditionsStr = conditionsStr+" AND do.auditing_status != '#{Constants::PASS_APPLY_CANCEL}'"
      end

    if conditionsStr != ""
      conditionsStr = "where " + conditionsStr
    end

    if params[:entry_info] == "true"
      #要对where再处理一下。以不显示数量为0即没有进货的入库详细表信息
      #      if conditionsStr != ""
      #        conditionsStr = conditionsStr + "AND ed.quantity is not null"
      #      else
      #        conditionsStr = "where ed.quantity is not null"
      #      end
    else
      #      purchaseOrders = PurchaseOrder.find(:all,:select=>"po.id as purchase_order_id,po.auditing_status,po.order_number as purchase_order_number,po.action_date ,po.auditing_status ,pd.quantity as purchase_quantity,p.name as product_name,ifnull(ed.quantity,0) as entry_quantity ,d.name as department_name,po.store_status,v.name as vendor_name",:joins =>"po
      #left join purchase_order_details pd on po.id = pd.purchase_order_id AND pd.is_locked = 0
      #left join (select ed.purchase_order_id,ed.sale_order_id,ed.product_id,sum(ed.quantity) quantity from warehousing_entries we
      #           left join warehousing_entry_details ed on we.id = ed.warehousing_entry_id where we.auditing_status != '#{Constants::PASS_APPLY_CANCEL}' group by ed.sale_order_id,ed.purchase_order_id,ed.product_id) ed
      #           on pd.sale_order_id = ed.sale_order_id AND pd.product_id = ed.product_id AND pd.purchase_order_id = ed.purchase_order_id
      #left join products p on p.id = pd.product_id
      #left join departments d on d.id = po.department_id
      #left join vendors v on v.id = po.vendor_id
      #{conditionsStr} group by pd.id,p.id")
      #warehousingEntries = WarehousingEntry.find(:all,:select=>"wed.id,we.order_number,we.auditing_status, wed.quantity ,we.updated_at,po.order_number as purchase_order_number,po.auditing_status as purchase_auditing_status,v.name as vendor_name,p.name as product_name,p.number as product_number,cp.cost_price as cost_price,cp.tax_price as tax_price,(wed.quantity * cp.cost_price) as cost_price_amount,sum(ROUND(wed.quantity * cp.cost_price,2)) as sum_cost_price_amount,sum(ROUND(wed.quantity * cp.tax_price,2)) as sum_tax_price_amount" ,:joins =>"we
      #  left join warehousing_entry_details wed on we.id = wed.warehousing_entry_id
      #  left join purchase_orders po on po.id = we.purchase_order_id
      #  left join products p on p.id = wed.product_id
      #  left join departments d on d.id = po.department_id
      #  left join vendors v on v.id = po.vendor_id
      #  left join cost_prices cp on cp.product_id = p.id AND cp.cal_year = #{calYear} AND cp.cal_month = #{calMonth}
      #  #{conditionsStr} group by wed.id,wed.product_id")

      saleDeliveries = DeliveryOrder.find(:all,:select=>"ss.name sales_strategy,st.name sale_type,dod.id,do.order_number,do.auditing_status, dod.quantity ,do.updated_at,so.order_number as sale_order_number,so.auditing_status as sale_auditing_status,c.name as customer_name,p.name as product_name,p.number as product_number,cp.cost_price as cost_price,cp.tax_price as tax_price,(dod.quantity * cp.cost_price) as cost_price_amount,sod.unit_price*sod.discount as sale_unit_price,sod.unit_price*sod.discount*sod.quantity as sale_amount,(dod.quantity * cp.cost_price) cost_price_amount,(dod.quantity * cp.tax_price) tax_price_amount" ,:joins =>"do
        left join delivery_order_details dod on do.id = dod.delivery_order_id
        left join sale_orders so on so.id = do.sale_order_id
        left join sale_order_details sod on so.id = sod.sale_order_id AND sod.product_id = dod.product_id
        left join sale_types st on st.id = so.sale_type_id
        left join sales_strategies ss on ss.id = so.sales_strategy_id
        left join products p on p.id = dod.product_id
        left join departments d on d.id = so.department_id
        left join customers c on c.id = so.customer_id
        left join cost_prices cp on cp.product_id = p.id AND cp.cal_year = #{calYear} AND cp.cal_month = #{calMonth}
        #{conditionsStr} group by dod.id,dod.product_id")
    end

    sumCostPriceAmount = 0.00
    sumTaxPriceAmount = 0.00
    if saleDeliveries.length > 0
      saleDeliveries.each do |deliveryOrder|
        sumCostPriceAmount = sumCostPriceAmount + deliveryOrder.cost_price_amount.to_f
        sumTaxPriceAmount = sumTaxPriceAmount + deliveryOrder.tax_price_amount.to_f
      end
    end
    #判断是输出报表，还是输出Json
    if params[:sub_type].nil?
      jsonStr="{totalProperty:"+saleDeliveries.length.to_s+",sum_cost_price_amount:#{sumCostPriceAmount},sum_tax_price_amount:#{sumTaxPriceAmount},root:"+saleDeliveries.to_json()+"}"
      logger.info jsonStr
      render :text=>jsonStr, :layout=>false
    else
      require 'spreadsheet'
      Spreadsheet.client_encoding = "UTF-8"
      bookString = StringIO.new
      book = Spreadsheet.open "#{Rails.root.to_s}/report_template/sales_cost_report.xls"
      i = 4
      sheet1 = book.worksheet 0
      sheet1[3,0] = "销售订单"
      sheet1[3,1] = "审批状态"
      sheet1[3,2] = "出库单"
      #sheet1[3,3] = "审批状态"
      sheet1[3,4] = "客户"
      sheet1[3,5] = "产品"
      sheet1[3,6] = "编号"
      sheet1[3,7] = "数量"      
      sheet1[3,8] = "销售单价"
      sheet1[3,9] = "销售总额"
      sheet1[3,10] = "销售类型"
      sheet1[3,11] = "销售策略"
      sheet1[3,12] = "出库日期"

      format = Spreadsheet::Format.new :color => :blue,
        :weight => :bold,
        :size => 18

      sheet1.default_format = format
      saleDeliveries.each do |saleDelivery|
        sheet1[i,0] = saleDelivery.sale_order_number
        sheet1[i,1] = saleDelivery.sale_auditing_status
        sheet1[i,2] = saleDelivery.order_number
        #sheet1[i,3] = saleDelivery.delivery_auditing_status
        sheet1[i,4] = saleDelivery.customer_name
        sheet1[i,5] = saleDelivery.product_name
        sheet1[i,6] = saleDelivery.product_number
        sheet1[i,7] = saleDelivery.quantity        
        sheet1[i,8] = saleDelivery.sale_unit_price
        sheet1[i,9] = saleDelivery.sale_amount
        sheet1[i,10] = saleDelivery.sale_type
        sheet1[i,11] = saleDelivery.sales_strategy
        sheet1[i,12] = saleDelivery.updated_at.strftime("%Y-%m-%d").to_s
        i += 1
      end

      #sheet1[i,9] = sumCostPriceAmount
      sheet1[i,9] = sumTaxPriceAmount

      book.write bookString
      send_data bookString.string,:filename => '销售出库分析.xls'
    end
  end

  #为采购，销售等提供简单的tooltip库存提示
  def simpleStockForToolTipByProductId
    productId = params[:product_id]
    @pcs = ProductCollection.joins("pc left join warehouses w on pc.warehouse_id = w.id").group("pc.product_id,pc.warehouse_id").where("pc.product_id=?",productId).select("w.name,sum(pc.quantity) quantity")
  end
end
